//货架的数据模型
const util = require("../../common/util");
// 创建 MySQL 连接池
const pool = require("../../common/util");

// 货架的数据库操作类
class ShelvesDB {
    constructor() {
        this.instance = null;
        this.pool = pool;
    }

    /**
     * 创建 ItemDB 对象
     * @returns ShelvesDB
     */
    static getInstance() {
        if (!this.instance) {
            this.instance = new ShelvesDB();
        }
        return this.instance;
    }
}

/**  
 * 增加货架信息  
 * @param {Object} shelves - 货架信息对象，
 * @returns {Promise<number>} - 插入成功的记录ID  
 */  
ShelvesDB.prototype.add = async function (shelves) {  
    try {  
        const sql = 'INSERT INTO shelves (warehouseZone_id, shelf_number, layers, shelf_type, shelf_size, shelf_capacity, shelf_status, shelf_location, description, photo_url) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?, ?)'; 
        const params =  [  
            shelves.warehouseZoneId,  
            shelves.shelfNumber,  
            shelves.layers,
            shelves.shelfType,
            shelves.shelfSize,
            shelves.shelfCapacity,
            shelves.shelfStatus,
            shelves.shelfLocation ,
            shelves.description , 
            shelves.photoUrl 
        ]
        const result = await this.pool.query(sql, params);  
        return result.affectedRows;  
    } catch (error) {  
        util.err('Error adding shelves information:', error);  
        throw error; // 重新抛出错误  
    }  
};

/**  
 * 更新货架信息  
 * @param {string} shelfId - 货架ID  
 * @param {Object} shelves - 货架更新信息对象  
 * @returns {Promise<number>} - 更新影响的记录数  
 */  
ShelvesDB.prototype.update = async function (shelf) {  
    try {  
        // 构建SQL语句和参数  
        const sql = 'UPDATE shelves SET ' +  
            'warehouseZone_id = ?, ' +  
            'shelf_number = ?, ' + 
            'layers = ?, ' +
            'shelf_type = ?, ' +  
            'shelf_size = ?, ' +  
            'shelf_capacity = ?, ' +  
            'shelf_status = ?, ' +  
            'shelf_location = ?, ' +  
            'description = ?, ' +  
            'photo_url = ? ' +  
            'WHERE id = ?';
    
        const params = [  
            shelf.warehouseZoneId,
            shelf.shelfNumber, 
            shelf.layers, 
            shelf.shelfType,  
            shelf.shelfSize,  
            shelf.shelfCapacity,  
            shelf.shelfStatus,  
            shelf.shelfLocation,  
            shelf.description,  
            shelf.photoUrl,  
            shelf.id // 最后一个参数是WHERE子句中的shelf_id  
        ];  
  
        // 执行更新操作  
        const result = await this.pool.query(sql, params);   
  
        // 通常，对于UPDATE操作，我们关心的是affectedRows（受影响的行数）  
        // 注意：在MySQL中，affectedRows在UPDATE成功后总是1（如果找到了匹配的行），即使你没有更改任何列的值  
        // 如果没有找到匹配的行，affectedRows将为0  
        return result;  
    } catch (error) {  
        util.err('Error updating shelves information:', error);  
        throw error; // 重新抛出错误  
    }  
};

/**查找指定货架
 * 
 */
ShelvesDB.prototype.find = async function (id) {
    try {
        const params = [id]
        const sql = 'SELECT * FROM shelves WHERE id = ?';
        const result = await this.pool.query(sql, params);
        return result[0];
    } catch (error) {
        util.err('Error finding shelves information:', error);
        throw error; // 同样重新抛出错误
    }
};

/**  
 * 查询货架中含有物品的数量列表  
 * @param {number} pageNumber 当前页数  
 * @param {number} pageSize 显示的记录数  
 * @returns {Promise<Array>}  
 */  
ShelvesDB.prototype.findAllWithPagination = async function (pageNumber, pageSize, sortOrder = 'ASC') {  
    try {  
        const offset = (pageNumber - 1) * pageSize;  
          
        // 检查sortOrder参数是否为有效的排序方式  
        if (['ASC', 'DESC'].indexOf(sortOrder) === -1) {  
            sortOrder = 'ASC'; // 如果不是'ASC'或'DESC'，则默认为升序  
        }  
  
        const sql = `  
            SELECT   
                shelves.*,  
                SUM(CASE WHEN shelf_items.status != '已取出' THEN 1 ELSE 0 END) AS items_count  
            FROM   
                shelves  
            LEFT JOIN   
                shelf_items ON shelves.id = shelf_items.shelf_id  
            GROUP BY   
                shelves.id  
            ORDER BY   
                items_count ${sortOrder},   
                shelves.id ASC             
            LIMIT ? OFFSET ?  
        `;  
        const result = await this.pool.query(sql, [pageSize, offset]);  
        return result;  
    } catch (error) {  
        util.err('Error finding shelves information:', error);  
        throw error; // 重新抛出错误  
    }  
};

/**
 * 通过货架类型货架货架列表
 * @returns 
 */

/**查找货架列表
 */
ShelvesDB.prototype.findAll = async function () {
    try {
        const params = []
        const sql = 'SELECT * FROM shelves';
        const result = await this.pool.query(sql, params);
        return result;
    } catch (error) {
        util.err('Error finding shelves information:', error);
        throw error; // 同样重新抛出错误
    }
};

/**
 * 删除指定货架
 * 
 */
ShelvesDB.prototype.remove = async function (id) {
    try {
        const sql = 'DELETE FROM shelves WHERE id = ?';
        const params = [id];
        const result = await this.pool.query(sql, params);
        console.log(result)
        return result;
    }catch (error) {
        util.err('Error removing shelves information:', error);
        throw error; // 同样重新抛出错误
    }
};

/**
 * 获取货架总数
 * 
 */
ShelvesDB.prototype.getCount = async function () {
    try {
        const sql = 'SELECT COUNT(*) as count FROM shelves';
        const result = await this.pool.query(sql);
        return result[0].count;
    }catch (error) {
        util.err('Error getting shelves count:', error);
        throw error; // 同样重新抛出错误
    }
};





module.exports = ShelvesDB;


